USE NTDB
GO

IF OBJECT_ID('tableOfTablesColumnsNTDB') IS NOT NULL
    DROP TABLE tableOfTablesColumnsNTDB
IF OBJECT_ID('tableOfTablesColumnsNTDB2') IS NOT NULL
    DROP TABLE tableOfTablesColumnsNTDB2

CREATE TABLE tableOfTablesColumnsNTDB (
RowID int not null primary key identity(1,1),
sName VARCHAR(50),
tName VARCHAR(50),
cName VARCHAR(50))

SET NOCOUNT ON
INSERT INTO tableOfTablesColumnsNTDB
SELECT s.name SchemaName, t.name TableName, c.name ColumnName
FROM sys.columns c INNER JOIN
     sys.tables t ON c.object_id = t.object_id INNER JOIN
     sys.schemas s ON t.schema_id = s.schema_id
     
WHERE t.name != 'tableOfTablesColumnsNTDB'

SELECT * FROM tableOfTablesColumnsNTDB

DECLARE @Column_id NVARCHAR(100)
DECLARE @Table_id NVARCHAR(100)
DECLARE @RowsToProcess  INT
DECLARE @CurrentRow     INT
DECLARE @sql NVARCHAR(4000)

SET @RowsToProcess=@@ROWCOUNT

CREATE TABLE tableOfTablesColumnsNTDB2 (
TableName NVARCHAR(100),
ColumnName NVARCHAR(100),
nRowsAll INT,
nRowsNonNull INT
)

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
	PRINT N'Current row is:';
	PRINT @CurrentRow
    SET @CurrentRow=@CurrentRow+1
    SELECT @Table_id = tName, @Column_id = cName FROM tableOfTablesColumnsNTDB WHERE @CurrentRow = RowID
   
    
	IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'INC_KEY' AND Object_ID = Object_ID(@Table_id))
		BEGIN
			SELECT @sql = N'SELECT @cnt_all = COUNT(DISTINCT INC_KEY), @cnt_nonNull = COUNT('+@Column_id+') FROM dbo.'+@Table_id
		END
	ELSE
		BEGIN
			SELECT @sql = N'SELECT @cnt_all = COUNT(*), @cnt_nonNull = COUNT('+@Column_id+') FROM dbo.'+@Table_id
		END
		
	INSERT tableOfTablesColumnsNTDB2 (TableName,ColumnName,nRowsAll,nRowsNonNull) 
						      EXEC('DECLARE @cnt_all INT, @cnt_nonNull INT
							  EXEC sp_executesql N''' + @sql + ''',
						      N''@Table_id nvarchar(100), @Column_id nvarchar(100),
							  @cnt_all INT OUTPUT, @cnt_nonNull INT OUTPUT'',
                              @Table_id = ''' + @Table_id + ''',
                              @Column_id = ''' + @Column_id + ''',
                              @cnt_all = @cnt_all OUTPUT,@cnt_nonNull = @cnt_nonNull OUTPUT
							  SELECT ''' + @Table_id+ ''',''' +@Column_id+''',@cnt_all, @cnt_nonNull')
         
--	SELECT @mycnt = cnt FROM #result
--    INSERT INTO tableOfTablesColumnsNTDB2(TableName,ColumnName) VALUES(@Table_id,@Column_id)
--    SELECT @sql = 'INSERT INTO tableOfTablesColumnsNTDB2(TableName, ColumnName) VALUES(' + @Table_id + ', ' + @Column_id+')'
--    PRINT @sql
--    EXEC(@sql)
--    EXECUTE sp_executesql @sql, @ParmDefinition, @CurrentRowVar = @CurrentRow;
--    EXECUTE sp_executesql N'INSERT INTO tableOfTablesColumnsNTDB2(TableName, ColumnName) VALUES(@Table_id,@Column_id)'
--	INSERT INTO tableOfTablesColumnsNTDB2(TableName,ColumnName,nRows) VALUES(@Table_id,@Column_id,)
END

SELECT * FROM tableOfTablesColumnsNTDB2

--SELECT a.TableName, a.ColumnName, b.nRows FROM tableOfTablesColumnsNTDB2 a, tableOfTablesColumnsNTDB3 b

/*
SELECT @Column_id = min(ColumnName) FROM tableOfTablesColumnsNTDB
SELECT @Table_id = min(TableName) FROM tableOfTablesColumnsNTDB

WHILE @Column_id IS NOT NULL
BEGIN
    SELECT * FROM tableOfTablesColumnsNTDB WHERE ColumnName = @Column_id
    SELECT @Column_id = MIN(ColumnName) FROM tableOfTablesColumnsNTDB WHERE ColumnName > @Column_id
END
*/
/*EXEC CountRowsInThisColumn 'ColumnName', 'TableName', 'tableOfTablesColumnsNTDB'*/
GO

DROP TABLE tableOfTablesColumnsNTDB
DROP TABLE tableOfTablesColumnsNTDB2